const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 数据库路径
const dbPath = path.join(__dirname, 'database.db');

// 连接数据库
const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('Error opening database:', err.message);
    return;
  }
  console.log('Connected to SQLite database.');
});

// 更新start_addr为1000及以后的记录
function updateSuzhouData() {
  console.log('正在查询start_addr >= 1000的记录...');
  
  // 首先查看当前数据
  db.all(
    `SELECT id, sequence_number, start_addr, type, variable, description, note 
     FROM config_data 
     WHERE CAST(start_addr AS INTEGER) >= 1000 
     ORDER BY id`,
    (err, rows) => {
      if (err) {
        console.error('查询失败:', err.message);
        return;
      }
      
      console.log('\n更新前的数据:');
      console.table(rows);
      
      if (rows.length === 0) {
        console.log('没有找到start_addr >= 1000的记录');
        db.close();
        return;
      }
      
      // 更新这些记录
      const updatePromises = rows.map((row, index) => {
        return new Promise((resolve, reject) => {
          // 根据记录类型和序号生成苏州相关的描述
          let newDescription = '';
          let newVariable = '';
          let newNote = '苏州卫水动态管控协议';
          
          switch(row.type) {
            case '2':
              newDescription = `苏州水质监测点${row.sequence_number}`;
              newVariable = `suzhou.water.monitor${row.sequence_number}`;
              break;
            case '4':
              newDescription = `苏州控制阀门${row.sequence_number}`;
              newVariable = `suzhou.valve.control${row.sequence_number}`;
              break;
            default:
              newDescription = `苏州设备${row.sequence_number}`;
              newVariable = `suzhou.device${row.sequence_number}`;
          }
          
          db.run(
            `UPDATE config_data 
             SET variable = ?, description = ?, note = ?
             WHERE id = ?`,
            [newVariable, newDescription, newNote, row.id],
            function(err) {
              if (err) {
                reject(err);
              } else {
                console.log(`已更新记录 ID ${row.id}: ${newDescription}`);
                resolve();
              }
            }
          );
        });
      });
      
      // 等待所有更新完成
      Promise.all(updatePromises)
        .then(() => {
          console.log('\n所有记录更新完成，查看更新后的数据:');
          
          // 查看更新后的数据
          db.all(
            `SELECT id, sequence_number, start_addr, type, variable, description, note 
             FROM config_data 
             WHERE CAST(start_addr AS INTEGER) >= 1000 
             ORDER BY id`,
            (err, updatedRows) => {
              if (err) {
                console.error('查询更新后数据失败:', err.message);
              } else {
                console.table(updatedRows);
                console.log(`\n成功更新了 ${updatedRows.length} 条记录`);
              }
              
              db.close((err) => {
                if (err) {
                  console.error('关闭数据库失败:', err.message);
                } else {
                  console.log('数据库连接已关闭');
                }
              });
            }
          );
        })
        .catch((err) => {
          console.error('更新失败:', err.message);
          db.close();
        });
    }
  );
}

// 执行更新
updateSuzhouData();